﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

/// <summary>
/// Summary description for DataAccess
/// </summary>
public class DataAccess
{
    SqlConnection conn;
    private SqlDataAdapter adapter;
    public SqlConnection Conn
    {
        get { return conn; }
        set { conn = value; }
    }
    public DataAccess()
    {
		//
		// TODO: Add constructor logic here
        //
    }
    public SqlConnection OpenConnect()
    {
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        try
        {
            if (conn.State == ConnectionState.Open) 
            {
                conn.Close();
                conn.Open();
            }
            else 
            {
                conn.Open();
            }
        }
        catch (Exception e) 
        {
            return null;
        }
        return conn;
    }
    public void CloseConnection(SqlConnection sql)
    {
        if (sql != null)
        {
            if (sql.State == ConnectionState.Open)
            {
                sql.Close();
                sql.Dispose();
            }
        }
    }

    /// <summary>
    /// Execute all store procedure without parammeter
    /// </summary>
    /// <param Name="spName">Sql store procedure Name</param>
    /// <returns>Sql data in a DataTable</returns>
    public DataTable executeSelect(string spName)
    {
        DataSet dataSet = new DataSet();
        //Open connection
        conn = OpenConnect();

        adapter = new SqlDataAdapter(spName, conn);
        adapter.SelectCommand.CommandType = CommandType.StoredProcedure;

        adapter.Fill(dataSet);

        //Close connection
        CloseConnection(conn);
        return dataSet.Tables[0];

    }

    /// <summary>
    /// Execute all store procedure has parammeter
    /// </summary>
    /// <param Name="spName">Sql store procedure Name</param>
    /// <param Name="paramList">Parameters of the store procedure</param>
    /// <returns>Sql data in a DataTable</returns>
    public DataTable executeSelect(string spName, SqlParameter[] paramList)
    {
        DataSet dataSet = new DataSet();
        //Open connection
        conn = OpenConnect();

        adapter = new SqlDataAdapter(spName, conn);
        adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
        adapter.SelectCommand.Parameters.AddRange(paramList);

        adapter.Fill(dataSet);
        //Close connection
        CloseConnection(conn);

        return dataSet.Tables[0];
    }


    /// <summary>
    /// Execute all store procedure performs insert, update, delete
    /// </summary>
    /// <param Name="spName">Sql store procedure Name</param>
    /// <param Name="paramList">Parameters of the store procedure</param>
    /// <returns>Sql data in a DataTable</returns>
    public SqlParameter[] initialization(int length_parameter)
    {
        SqlParameter[] parameter = new SqlParameter[length_parameter];
        for (int i = 0; i < length_parameter; i++)
        {
            parameter[i] = AddParameter("Null" + i, DBNull.Value, SqlDbType.Int,null);
        }
        return parameter;
    }
    public int executeUpdate(string spName, SqlParameter[] paramList)
    {
        int result = 0;
        //Open connection
        conn = OpenConnect();

        SqlCommand command = new SqlCommand(spName, conn);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddRange(paramList);

        try
        {
            result = command.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            //relationship error
            result = 0;
        }
        //Close connection
        CloseConnection(conn);

        return result;
    }

    /// <summary>
    /// Execute store procedure get result executed.
    /// </summary>
    /// <param Name="spName"></param>
    /// <param Name="paramList"></param>
    /// <returns></returns>
    public int executeScalary(string spName, SqlParameter[] paramList)
    {
        int result = 0;
        //Open connection
        conn = OpenConnect();

        SqlCommand command = new SqlCommand(spName, conn);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddRange(paramList);

        result = int.Parse(command.ExecuteScalar().ToString());
        //Close connection
        CloseConnection(conn);

        return result;

    }
    public SqlParameter AddParameter(string parameterName, object parameterValue, SqlDbType parameterSqlType, object size)
    {
        SqlParameter parameter;
        if (!parameterName.StartsWith("@"))
        {
            parameterName = "@" + parameterName;
        }
        if (size != null)
        {
            parameter = new SqlParameter(parameterName, parameterSqlType, Convert.ToInt32(size));
        }
        else
        {
            parameter = new SqlParameter(parameterName, parameterSqlType);
        }
        parameter.Value = parameterValue;
        return parameter;

    }
    
}